"""
Case Type   : PACKAGE
Case Name   : 创建package,系统管理员对普通用户赋予package的权限后用户对package
              进行操作
Create At   : 2022/04/26
Owner       : @zou_jialiang050
Description :
    1.创建用户
    2.声明包规格
    3.定义package body
    4.调用package中存储过程,查询表数据
    5.将package的所有权限以及create any package权限赋给新建用户
    6.新建用户身份进入数据库,切换至public模式重新声明定义原本package
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.调用package成功,返回执行结果
    5.成功
    6.合理报错
    7.成功
History     :
"""

import os
import unittest
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class PACKAGE(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.user = 'u_package_0039'
        self.package = 'p_test_0039'
        self.procedure = 'p_package_0039_01'
        self.procedure1 = 'p_package_0039_02'
        self.table = 't_package_0039'

    def test_package(self):
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        step = 'step1:创建用户 expect:成功'
        self.logger.info(step)
        create_user = self.primary_sh.execut_db_sql(f'''
            drop user if exists {self.user} cascade;
            create user {self.user} with password '{macro.PASSWD_REPLACE}';''')
        self.logger.info(create_user)
        self.assertIn(self.Con.CREATE_ROLE_SUCCESS_MSG, create_user,
                      '执行失败' + step)

        step = 'step2:声明package expect:成功'
        self.logger.info(step)
        spec_package = self.primary_sh.execut_db_sql(
            f'''create or replace package {self.package} is
            procedure {self.procedure}();
            end {self.package};''')
        self.logger.info(spec_package)
        self.assertIn(self.Con.create_package_success_msg, spec_package,
                      '执行失败' + step)

        step = 'step3:定义package body expect:成功'
        self.logger.info(step)
        def_package = self.primary_sh.execut_db_sql(
            f'''create or replace package body {self.package} is
            procedure {self.procedure}()
            is
            begin
               drop table if exists {self.table};
               create table {self.table}(col_text text);
               insert into {self.table} values('test');
            end;
            end {self.package};''')
        self.logger.info(def_package)
        self.assertIn(self.Con.create_package_body_success_msg, def_package,
                      '执行失败' + step)

        step = 'step4:调用package,查询表数据 expect:调用成功,返回执行结果'
        self.logger.info(step)
        invoke_package = self.primary_sh.execut_db_sql(
            f'''call {self.package}.{self.procedure}();
            select * from {self.table};''')
        self.logger.info(invoke_package)
        self.assertIn('test', invoke_package, '执行失败' + step)

        step = 'step5:将package的所有权限以及create any package权限赋给新建用' \
               '户 expect:成功'
        self.logger.info(step)
        grant_pri = self.primary_sh.execut_db_sql(f'''
            grant all privileges on package {self.package} to {self.user};
            grant create any package to {self.user};''')
        self.logger.info(grant_pri)
        self.assertEqual(grant_pri.count('GRANT'), 2, '执行失败' + step)

        step = 'step6:新建用户身份进入数据库,切换至public模式重新声明定义原本' \
               'package expect:合理报错'
        self.logger.info(step)
        spec_def_package = self.primary_sh.execut_db_sql(
            f'''set current_schema = public;
            create or replace package {self.package} is
            procedure {self.procedure1}();
            end {self.package};

            create or replace package body {self.package} is
            procedure {self.procedure1}()
            is
            begin
               drop table if exists {self.table};
               create table {self.table}(col_text text);
               insert into {self.table} values('test1');
            end;
            end {self.package};''', sql_type=f" -U {self.user} -W "
            f"'{macro.PASSWD_REPLACE}'")
        self.logger.info(spec_def_package)
        self.assertIn('SET', spec_def_package, '执行失败' + step)
        self.assertIn(r'ERROR:  security definer package not ' \
            r'support replace with different owner',
                      spec_def_package, '执行失败' + step)

    def tearDown(self):
        step = 'step7:清理环境 expect:清理环境成功'
        self.logger.info(step)
        clean_environment = self.primary_sh.execut_db_sql(
            f'''drop package {self.package};
            drop user {self.user} cascade;''')
        self.logger.info(clean_environment)
        self.assertIn(self.Con.drop_package_success_msg, clean_environment,
                      '执行失败' + step)
        self.assertIn(self.Con.DROP_ROLE_SUCCESS_MSG, clean_environment,
                      '执行失败' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
